/*============================================================================
  File:     AlterSchemaToDBO.sql

  Summary:  Changes the schema to dbo for each table in the AdventureWorks 
            sample database.

  Date:     April 01, 2005
------------------------------------------------------------------------------
  This file is part of the Microsoft SQL Server Code Samples.

  Copyright (C) Microsoft Corporation.  All rights reserved.

  This source code is intended only as a supplement to Microsoft
  Development Tools and/or on-line documentation.  See these other
  materials for detailed information regarding Microsoft code samples.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- Change schemas to dbo
USE AdventureWorks;
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

-- Must drop the schema bound views first
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[Production].[vProductAndDescription]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [Production].[vProductAndDescription];
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[Person].[vStateProvinceCountryRegion]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [Person].[vStateProvinceCountryRegion];
GO

ALTER SCHEMA [dbo] TRANSFER [Person].[vAdditionalContactInfo];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[vEmployee];
ALTER SCHEMA [dbo] TRANSFER [Sales].[vIndividualCustomer];
ALTER SCHEMA [dbo] TRANSFER [Sales].[vIndividualDemographics];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[vJobCandidate];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[vJobCandidateEducation];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[vJobCandidateEmployment];
ALTER SCHEMA [dbo] TRANSFER [Production].[vProductModelCatalogDescription];
ALTER SCHEMA [dbo] TRANSFER [Production].[vProductModelInstructions];
ALTER SCHEMA [dbo] TRANSFER [Sales].[vSalesPerson];
ALTER SCHEMA [dbo] TRANSFER [Sales].[vStoreWithDemographics];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[vVendor];
GO

ALTER SCHEMA [dbo] TRANSFER [Person].[Address];
ALTER SCHEMA [dbo] TRANSFER [Person].[AddressType];
ALTER SCHEMA [dbo] TRANSFER [Production].[BillOfMaterials];
ALTER SCHEMA [dbo] TRANSFER [Person].[Contact];
ALTER SCHEMA [dbo] TRANSFER [Sales].[ContactCreditCard];
ALTER SCHEMA [dbo] TRANSFER [Person].[ContactType];
ALTER SCHEMA [dbo] TRANSFER [Sales].[CountryRegionCurrency];
ALTER SCHEMA [dbo] TRANSFER [Person].[CountryRegion];
ALTER SCHEMA [dbo] TRANSFER [Sales].[CreditCard];
ALTER SCHEMA [dbo] TRANSFER [Production].[Culture];
ALTER SCHEMA [dbo] TRANSFER [Sales].[Currency];
ALTER SCHEMA [dbo] TRANSFER [Sales].[CurrencyRate];
ALTER SCHEMA [dbo] TRANSFER [Sales].[Customer];
ALTER SCHEMA [dbo] TRANSFER [Sales].[CustomerAddress];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[Department];
ALTER SCHEMA [dbo] TRANSFER [Production].[Document];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[Employee];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[EmployeeAddress];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[EmployeeDepartmentHistory];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[EmployeePayHistory];
ALTER SCHEMA [dbo] TRANSFER [Production].[Illustration];
ALTER SCHEMA [dbo] TRANSFER [Sales].[Individual];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[JobCandidate];
ALTER SCHEMA [dbo] TRANSFER [Production].[Location];
ALTER SCHEMA [dbo] TRANSFER [Production].[Product];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductCategory];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductCostHistory];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductDescription];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductDocument];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductInventory];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductListPriceHistory];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductModel];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductModelIllustration];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductModelProductDescriptionCulture];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductPhoto];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductProductPhoto];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductReview];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductSubcategory];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[ProductVendor];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[PurchaseOrderDetail];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[PurchaseOrderHeader];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesOrderDetail];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesOrderHeader];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesOrderHeaderSalesReason];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesPerson];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesPersonQuotaHistory];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesReason];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesTaxRate];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesTerritory];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesTerritoryHistory];
ALTER SCHEMA [dbo] TRANSFER [Production].[ScrapReason];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[Shift];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[ShipMethod];
ALTER SCHEMA [dbo] TRANSFER [Sales].[ShoppingCartItem];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SpecialOffer];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SpecialOfferProduct];
ALTER SCHEMA [dbo] TRANSFER [Person].[StateProvince];
ALTER SCHEMA [dbo] TRANSFER [Sales].[Store];
ALTER SCHEMA [dbo] TRANSFER [Sales].[StoreContact];
ALTER SCHEMA [dbo] TRANSFER [Production].[TransactionHistory];
ALTER SCHEMA [dbo] TRANSFER [Production].[TransactionHistoryArchive];
ALTER SCHEMA [dbo] TRANSFER [Production].[UnitMeasure];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[Vendor];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[VendorAddress];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[VendorContact];
ALTER SCHEMA [dbo] TRANSFER [Production].[WorkOrder];
ALTER SCHEMA [dbo] TRANSFER [Production].[WorkOrderRouting];
GO


-- Recreate the indexed views

CREATE VIEW [dbo].[vProductAndDescription] 
WITH SCHEMABINDING 
AS 
-- View (indexed or standard) to display products and product descriptions by language.
SELECT 
    p.[ProductID] 
    ,p.[Name] 
    ,pm.[Name] AS [ProductModel] 
    ,pmx.[CultureID] 
    ,pd.[Description] 
FROM [dbo].[Product] p 
    INNER JOIN [dbo].[ProductModel] pm 
    ON p.[ProductModelID] = pm.[ProductModelID] 
    INNER JOIN [dbo].[ProductModelProductDescriptionCulture] pmx 
    ON pm.[ProductModelID] = pmx.[ProductModelID] 
    INNER JOIN [dbo].[ProductDescription] pd 
    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
GO

-- Index the view
CREATE UNIQUE CLUSTERED INDEX [IX_vProductAndDescription] ON [dbo].[vProductAndDescription]([CultureID], [ProductID]);
GO

CREATE VIEW [dbo].[vStateProvinceCountryRegion] 
WITH SCHEMABINDING 
AS 
SELECT 
    sp.[StateProvinceID] 
    ,sp.[StateProvinceCode] 
    ,sp.[IsOnlyStateProvinceFlag] 
    ,sp.[Name] AS [StateProvinceName] 
    ,sp.[TerritoryID] 
    ,cr.[CountryRegionCode] 
    ,cr.[Name] AS [CountryRegionName]
FROM [dbo].[StateProvince] sp 
    INNER JOIN [dbo].[CountryRegion] cr 
    ON sp.[CountryRegionCode] = cr.[CountryRegionCode];
GO

-- Index the view
CREATE UNIQUE CLUSTERED INDEX [IX_vStateProvinceCountryRegion] ON [dbo].[vStateProvinceCountryRegion]([StateProvinceID], [CountryRegionCode]);
GO

SET ANSI_NULLS OFF;
GO
SET QUOTED_IDENTIFIER OFF;
GO

USE master;
GO
